package com.goldgov.code.meta.source.impl;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.goldgov.code.exception.MetaSourceException;
import com.goldgov.code.meta.Constraint;
import com.goldgov.code.meta.FieldInfo;
import com.goldgov.code.meta.FieldType;
import com.goldgov.code.meta.ModuleInfo;
import com.goldgov.code.meta.semantics.SemanticsTranslate;
import com.goldgov.code.meta.source.MetaSource;
import com.goldgov.code.utils.StringUtils;

public class SqlMetaSource implements MetaSource{
	
	public static final String CREATE_TABLE_PREFIX = "CREATE TABLE";
	
	private String filedType = "((VARCHAR2|VARCHAR|DECIMAL|DATETIME|DATE|TIMESTAMP|INT|BIGINT|TINYINT)\\s*(\\(\\s*\\d+\\s*(,\\s*\\d+\\s*)?\\))?)";
	private String notNull = "(NOT NULL)?";
	private String comment = "(COMMENT\\s+(\\S+))?";
	
	private Pattern pattern = Pattern.compile("(\\S+)\\s+"+filedType+"\\s*" + notNull + comment + "\\s*,?");
	
	private String sql;

	private SemanticsTranslate translate;
	
	public SqlMetaSource(File tablesSql) throws FileNotFoundException {
		this(new FileInputStream(tablesSql));
	}
	
	public SqlMetaSource(InputStream tablesSql) {
		try(BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(tablesSql,"GBK"))){
			sql = getSqlScript(bufferedReader);
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
	}
	
	public SqlMetaSource(String tablesSql) {
		this.sql = tablesSql;
	}
	
	public String getSqlScript(BufferedReader bufferedReader) {
		try{
			String line = null;
			StringBuilder strBuilder = new StringBuilder();
			try {
				while((line = bufferedReader.readLine()) != null) {
					if(line.startsWith("--")) {
						continue;
					}
					strBuilder.append(line);
				}
			} catch (IOException e) {
				throw new MetaSourceException("无法根据数据流解析成SQL脚本",e);
			}
			return strBuilder.toString();
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return "";
		
	}
	
	private ModuleInfo[] resolve(String tablesSql,String prefix) {
		tablesSql = getSqlScript(new BufferedReader(new StringReader(tablesSql)));
		tablesSql = tablesSql.toUpperCase();
		String[] sqlStatement = tablesSql.split("[;]");
		
		if(sqlStatement.length == 0) {
			throw new MetaSourceException("提供的SQL脚本中没有任何语句:" + tablesSql);
		}
		
		List<ModuleInfo> moduleList = new ArrayList<>();
		for (String sql : sqlStatement) {
			sql = sql.trim();
			if(sql.startsWith(CREATE_TABLE_PREFIX)) {
				moduleList.add(resolveModule(sql,prefix));
			}
		}
		
		
		return moduleList.toArray(new ModuleInfo[0]);
	}
	
	private ModuleInfo resolveModule(String tablesSql,String prefix) {
		int fieldBeginIndex = tablesSql.indexOf('(');
		int fieldEndIndex = tablesSql.lastIndexOf(')');
		
		if(fieldBeginIndex < 0 && fieldEndIndex < 0) {
			throw new MetaSourceException("表创建语句不合法:" + tablesSql);
		}
		ModuleInfo moduleInfo = new ModuleInfo();
		String tableName = clearQuote(tablesSql.substring(CREATE_TABLE_PREFIX.length(), fieldBeginIndex));
		
		moduleInfo.setTableName(tableName);
		moduleInfo.setExplain(translateField(tableName));
		if(prefix != null && tableName.toUpperCase().startsWith(prefix.toUpperCase())){
			moduleInfo.setName(StringUtils.upperFirstChar(StringUtils.lowerCaseWithSeparator(tableName.substring(prefix.length()), "_")));
		}
		
		String fieldSql = tablesSql.substring(fieldBeginIndex + 1,fieldEndIndex);
		FieldInfo[] fields = resolveField(fieldSql,prefix);
		moduleInfo.setFields(fields);
		
		return moduleInfo;
	}

	private FieldInfo[] resolveField(String fieldSql,String prefix) {
		String[] pkFields = findPk(fieldSql);
		Matcher m = pattern.matcher(fieldSql);
		List<FieldInfo> result = new ArrayList<>();
		while (m.find()) {
//			System.out.println("\t字段名："+m.group(1) + "\t字段类型：" + m.group(2) + "\t是否必填：" + m.group(6) + "\t注释：" +m.group(8));
			FieldInfo fi = new FieldInfo();
			
			String columnName = clearQuote(m.group(1));
			String attName = columnName;
			if(prefix != null && columnName.toUpperCase().startsWith(prefix)){
				attName = columnName.substring(prefix.length());
			}
			fi.setField(columnName);
			fi.setName(StringUtils.lowerCaseWithSeparator(attName, "_"));
			
			setFieldType(fi,m.group(2));
			
			Constraint constraint = new Constraint();
			constraint.setControlType("Input");
			fi.setConstraint(constraint);
			if(m.group(6) != null) {
				constraint.setRequired(true);
			}
			String comment = m.group(8);
			if(comment != null) {
				fi.setExplain(clearQuote(comment));
			}else {
				fi.setExplain(translateField(fi.getField()));
			}
			
			for (String pk : pkFields) {
				if(fi.getField().equals(pk)) {
					constraint.setPrimaryKey(true);
				}
			}
			result.add(fi);
		}
		
		return result.toArray(new FieldInfo[0]);
	}
	
	private String[] findPk(String fieldSql) {
		int pkSign = fieldSql.toUpperCase().indexOf("PRIMARY KEY");
		if(pkSign > -1) {
			int pkStart = fieldSql.indexOf("(",pkSign);
			int pkEnd = fieldSql.indexOf(")",pkSign);
			if(pkStart > -1 && pkEnd > -1) {
				String pkInfo = fieldSql.substring(pkStart+1, pkEnd);
				String[] pkField = pkInfo.split(",");
				for (int i = 0; i < pkField.length; i++) {
					pkField[i] = pkField[i].trim();
				}
				return pkField;
			}
		}
		
		return new String[0];
	}

	public void setFieldType(FieldInfo fieldInfo,String type) {
		int lengthIndex = type.indexOf('(');
		if(lengthIndex < 0) {
			fieldInfo.setType(Enum.valueOf(FieldType.class, type.trim()));
		}else {
			FieldType fieldType = Enum.valueOf(FieldType.class, type.substring(0,lengthIndex));
			String[] typeLength = type.substring(lengthIndex + 1, type.length() - 1).split(",");
			fieldInfo.setLength(Integer.parseInt(typeLength[0]));
			if(typeLength.length > 1) {
				fieldInfo.setScale(Integer.parseInt(typeLength[1].trim()));
			}
			fieldInfo.setType(fieldType);
		}
	}
	

	private String clearQuote(String str) {
		str = str.replaceAll("[`'\"]", "");
		return str.trim();
	}
	
	@Override
	public ModuleInfo[] resolve() {
		return resolve(sql,null);
	}
	
	@Override
	public ModuleInfo[] resolve(String prefix) {
		if(prefix != null) {prefix = prefix.toUpperCase();}
		return resolve(sql,prefix);
	}
	
	@Override
	public void setSemanticsTranslate(SemanticsTranslate translate) {
		this.translate = translate;
	}
	
	private String translateField(String text) {
		if(translate != null) {
			StringBuilder strBuilder = new StringBuilder();
			String[] splitInfo = text.split("_");
			boolean canTranslate = false;
			for (String str : splitInfo) {
				String translateResult = translate.translate(str);
				if(translateResult.equals(str)) {
					translateResult = StringUtils.upperFirstChar(str.toLowerCase());
				}
				strBuilder.append(translateResult);
				if(!canTranslate && !translateResult.equals(str)) {
					canTranslate = true;
				}
				
			}
			return canTranslate ? strBuilder.toString() : null;
		}
		return null;
	}
	
//	public static void main(String[] args) throws Exception {
//		SqlMetaSource sqlMetaSource = new SqlMetaSource(new File("C:\\Users\\LHG\\Desktop\\cap.sql"));
//		ModuleInfo[] resolve = sqlMetaSource.resolve();
//		for (ModuleInfo moduleInfo : resolve) {
//			System.out.println("==================================");
//			System.out.println(moduleInfo.getTableName());
//			System.out.println("==================================");
//			FieldInfo[] fields = moduleInfo.getFields();
//			for (int i = 0; i < fields.length; i++) {
//				System.out.println(fields[i].getType() + "\t" + fields[i].getField()+"\t"+fields[i].getName());
//			}
//		}
//	}

}
